/**
 * futures : masterlink.futures.dao.AccountDao.java
 */
package masterlink.futures.dao;

import java.util.List;

import masterlink.futures.vo.Account;
import masterlink.futures.vo.Fdeals;
import masterlink.futures.vo.FdealsGroups;
import masterlink.futures.vo.Group;

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Repository;

/**
 * TODO Write the description
 *
 * @author : tonyyang
 * Create date : 2011/10/25
 * @version 1.0.0	
 */
@Repository
@Qualifier("FdealsDao")
public class FdealsDao extends TimesTenTemplateDao {
	
	private static final String FIND_BY_GROUP="select c.trid,c.buysell"+
			" , sum(c.quantity) as quantity, sum(c.quantity*c.dealprice1)/sum(c.quantity) as dealprice1"+
			" , sum(c.quantity*c.dealprice2)/sum(c.quantity) as dealprice2, sum(c.quantity*c.avgprice)/sum(c.quantity) as avgprice"+
			" from greeks_limit a, fuminv b, fdeals c"+
			" where a.account=b.acno and a.broker=b.broker_id"+ 
			" and b.broker_id=c.broker_id and b.acno=c.acno"+
			" and a.id=?"+
			" group by c.trid,c.buysell"+
			" order by c.trid";

	private static final String FIND_BY_TRID="select b.name,c.broker_id,c.acno, c.trid,c.buysell"+
			" , sum(c.quantity) as quantity, sum(c.quantity*c.dealprice1)/sum(c.quantity) as dealprice1"+
			" , sum(c.quantity*c.dealprice2)/sum(c.quantity) as dealprice2, sum(c.quantity*c.avgprice)/sum(c.quantity) as avgprice"+
			" from greeks_limit a, fuminv b, fdeals c"+
			" where a.account=b.acno and a.broker=b.broker_id"+ 
			" and b.broker_id=c.broker_id and b.acno=c.acno"+
			" and a.id=? "+
			" and c.trid=? and  c.buysell=?"+
			" group by b.name,c.broker_id,c.acno, c.trid,c.buysell"+
			" order by c.acno";

	private static final String FIND_BY_ACCOUNT="select b.name,c.broker_id,c.acno, c.trid,c.buysell"+
			" , sum(c.quantity) as quantity, sum(c.quantity*c.dealprice1)/sum(c.quantity) as dealprice1"+
			" , sum(c.quantity*c.dealprice2)/sum(c.quantity) as dealprice2, sum(c.quantity*c.avgprice)/sum(c.quantity) as avgprice"+
			" from greeks_limit a, fuminv b, fdeals c"+
			" where a.account=b.acno and a.broker=b.broker_id"+ 
			" and b.broker_id=c.broker_id and b.acno=c.acno"+
			" and a.id=? "+
			" and a.account=?"+
			" group by b.name,c.broker_id,c.acno, c.trid,c.buysell"+
			" order by c.acno";
	
	public List<FdealsGroups> findByGroup(Group g){
		if(g==null){
			log.warn("The given Group is null for findByGroup(Group g).");
			return null;
		}
		return findBySql(FIND_BY_GROUP, FdealsGroups.class, g.getId());
	}
	
	public List<Fdeals> findByTrid(Group g,String trid,String bs){
		if(g==null){
			log.warn("The given Group is null for findByGroup(Group g).");
			return null;
		}
		return findBySql(FIND_BY_TRID, Fdeals.class, g.getId(),trid,bs);
	}
	
	public List<Fdeals> findByAcc(Group g,Account acc){
		if(g==null){
			log.warn("The given Group is null for findByGroup(Group g).");
			return null;
		}
		return findBySql(FIND_BY_ACCOUNT, Fdeals.class, g.getId(),acc.getId().getAcno());
	}
	
}
